''' 滴滴打灰后端应用服务 数据库操作API（WMS管理） '''
import sys,traceback,logging,datetime,os
import pymysql
from dbutils.pooled_db import PooledDB 
from utils.db_config import mysqlInfo
from jinja2 import Environment, FileSystemLoader
import pdfkit
from utils.didiutils import strval,getfilesize 

logger = logging.getLogger('wmsservice')


# 创建数据库连接
def connect_db():
    pool=PooledDB(creator=pymysql,mincached=2, maxcached=5,maxshared=3, maxconnections=6, blocking=True,host=mysqlInfo['host'],port=mysqlInfo['port'],
        user=mysqlInfo['user'],password=mysqlInfo['password'],database=mysqlInfo['database'],charset=mysqlInfo['charset'])
    return pool.connection()

# 删除浏览记录
def deleteBrowse(param):
    conn = connect_db()
    cur = conn.cursor()
    cnt = 0
    try:
        sql = " delete from wms_browse where id = %s "
        cnt = cur.execute(sql,(param['id']))
        conn.commit()
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return cnt
    
# 新增/更新浏览记录
def addOrUpdateBrowse(param):
    conn = connect_db()
    cur = conn.cursor()
    cnt = 0
    try:
        if param['type'] == 1:
            sql = " update wms_browse set type=%s, member_id=%s, com_member_id=%s, company_id=%s, resume_id=%s, update_time=%s where member_id = %s AND resume_id = %s   "
            cnt = cur.execute(sql,(param['type'],param['memberId'],param['comMemberId'],param['companyId'],param['resumeId'],datetime.datetime.now(),param['memberId'],param['resumeId']))
            if cnt == 0:
                sql = "insert into wms_browse( type, member_id ,  com_member_id ,  company_id , resume_id,  create_time  ) values (%s, %s, %s, %s, %s, %s)"
                cnt = cur.execute(sql,(param['type'],param['memberId'],param['comMemberId'],param['companyId'],param['resumeId'],datetime.datetime.now()))
        else:
            sql = "update wms_browse set member_id = %s, com_member_id = %s, position_id = %s , tcode = %s , company_id = %s, update_time = %s where  " \
                  " member_id = %s and com_member_id = %s and position_id = %s and company_id = %s "  
            cnt = cur.execute(sql,(param['memberId'],param['comMemberId'],param['positionId'],param['tcode'],param['companyId'],datetime.datetime.now()
                                   ,param['memberId'],param['comMemberId'],param['positionId'],param['companyId']))
            if cnt == 0:
                sql = "insert into wms_browse(  member_id ,  com_member_id ,  position_id ,  tcode ,  company_id ,  create_time  ) values (%s, %s, %s, %s, %s, %s, %s)"
                cnt = cur.execute(sql,(param['memberId'],param['comMemberId'],param['positionId'],param['tcode'],param['companyId'],datetime.datetime.now()))
        conn.commit()
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return cnt

# 查询浏览记录列表
def getBrowseList(param):
    conn = connect_db()
    cur = conn.cursor()
    browselist = []
    try:
        blist = []
        sql = ""
        if param['type'] == '0':
            sql = " select id AS id, type AS type, member_id AS memberId, tcode AS tcode,com_member_id AS comMemberId, position_id AS positionId, company_id AS companyId " \
                  ", resume_id AS resumeId, status AS status, remark AS remark from wms_browse where 1 = 1 and member_id = %s and type = 0 order by create_time desc "
        else:
            sql = " select id AS id, type AS type, member_id AS memberId, tcode AS tcode,com_member_id AS comMemberId, position_id AS positionId, company_id AS companyId " \
                  ", resume_id AS resumeId, status AS status, remark AS remark from wms_browse where 1 = 1 and com_member_id = %s and type = 1 order by create_time desc "
        cur.execute(sql,(param['memberId']))
        for row in cur.fetchall():
            browse = {}
            browse['id'] = row[0]
            browse['type'] = row[1]
            browse['memberId'] = row[2]
            browse['tcode'] = row[3]
            browse['comMemberId'] = row[4]
            browse['positionId'] = row[5]
            browse['companyId'] = row[6]
            browse['resumeId'] = row[7]
            browse['status'] = row[8]
            browse['remark'] = row[9]
            blist.append(browse)
        for browse in blist:
            if browse['positionId'] is not None:
                # 职位信息
                sql = " select id AS id, company_id AS companyId,browse_number AS browseNumber,skill AS skill,if_parttime AS ifParttime,salary AS salary,address_name AS addressName,address_house AS addressHouse " \
                      "   , post_id AS postId, post_name AS postName, descr AS descr, address AS address, longitude AS longitude, latitude AS latitude, exp_require AS expRequire, min_education AS minEducation " \
                      "   , salary_low AS salaryLow, salary_up AS salaryUp, salary_month AS salaryMonth, unit AS unit, bonus AS bonus, settlement_type AS settlementType, recruits_number AS recruitsNumber " \
                      "   , part_date AS partDate, part_time AS partTime, phone AS phone, position_type AS positionType, start_date AS startDate, end_date AS endDate, work_days AS workDays, share_number AS shareNumber " \
                      "   ,share_money AS shareMoney, city AS city, citycode AS citycode, pcity AS pcity, pcitycode AS pcitycode, status AS status, remark AS remark from rms_company_position where 1 = 1 AND id = %s  "
                cur.execute(sql,(browse['positionId']))
                for row in cur.fetchall():
                    position = {}
                    position['id'] = row[0]
                    position['companyId'] = row[1]
                    position['browseNumber'] = row[2]
                    position['skill'] = row[3]
                    position['ifParttime'] = row[4]
                    position['salary'] = row[5]
                    position['addressName'] = row[6]
                    position['addressHouse'] = row[7]
                    position['postId'] = row[8]
                    position['postName'] = row[9]
                    position['descr'] = row[10]
                    position['address'] = row[11]
                    position['longitude'] = row[12]
                    position['latitude'] = row[13]
                    position['expRequire'] = row[14]
                    position['minEducation'] = row[15]
                    position['salaryLow'] = row[16]
                    position['salaryUp'] = row[17]
                    position['salaryMonth'] = row[18]
                    position['unit'] = row[19]
                    position['bonus'] = row[20]
                    position['settlementType'] = row[21]
                    position['recruitsNumber'] = row[22]
                    position['partDate'] = row[23]
                    position['partTime'] = row[24]
                    position['phone'] = row[25]
                    position['positionType'] = row[26]
                    position['startDate'] = row[27]
                    position['endDate'] = row[28]
                    position['workDays'] = row[29]
                    position['shareNumber'] = row[30]
                    position['shareMoney'] = row[31]
                    position['city'] = row[32]
                    position['citycode'] = row[33]
                    position['pcity'] = row[34]
                    position['pcitycode'] = row[35]
                    position['status'] = row[36]
                    position['remark'] = row[37]
                browse['position'] = position  
                # 招聘者信息
                sql = " select post_name AS postName, name AS name, avatar AS avatar from ums_member where if_del = 0 and member_id = %s LIMIT 1  "
                cur.execute(sql,(browse['comMemberId']))
                for row in cur.fetchall():
                    browse['memberPostName'] = row[0]
                    browse['memberName'] = row[1]
                    browse['memberAvatar'] = row[2]
                    
                # 企业信息
                sql = " select name,staff_size,nature from rms_company where id=%s   "
                cur.execute(sql,(browse['companyId']))                
                for row in cur.fetchall():
                    browse['companyName'] = row[0]
                    browse['companyStaffSize'] = row[1]
                    browse['companyNature'] = row[2]
                    
            if browse['resumeId'] is not None:
                # 求职者简历信息
                sql = " select id AS id, name AS name, sex AS sex,email AS email,browse_number AS browseNumber,skills AS skills,member_id AS memberId, phone AS phone, avatar AS avatar, education AS education " \
                      "   , time_to_work AS timeToWork, work_year AS workYear, birthday AS birthday, salary_low AS salaryLow, salary_up AS salaryUp, company AS company, post_id AS postId " \
                      "   , personal_skill AS personalSkill, job_status AS jobStatus, status AS status, remark AS remark " \
                      " from wms_resume where 1 = 1 and member_id = %s " 
                cur.execute(sql,(browse['memberId']))
                for row in cur.fetchall():
                    resume = {}
                    resume['id'] = row[0]
                    resume['name'] = row[1]
                    resume['sex'] = row[2]
                    resume['email'] = row[3]
                    resume['browseNumber'] = row[4]
                    resume['skills'] = row[5]
                    resume['memberId'] = row[6]
                    resume['phone'] = row[7]
                    resume['avatar'] = row[8]
                    resume['education'] = row[9]
                    resume['timeToWork'] = row[10]
                    resume['workYear'] = row[11]
                    resume['birthday'] = row[12]
                    resume['salaryLow'] = row[13]
                    resume['salaryUp'] = row[14]
                    resume['company'] = row[15]
                    resume['postId'] = row[16]
                    resume['personalSkill'] = row[17]
                    resume['jobStatus'] = row[18]
                    resume['status'] = row[19]
                    resume['remark'] = row[20]
                    collect['resume'] = resume

                # 求职者信息
                sql = " select post_name AS postName, name AS name, avatar AS avatar from ums_member where if_del = 0 and member_id = %s LIMIT 1  "
                cur.execute(sql,(browse['memberId']))
                for row in cur.fetchall():
                    browse['memberPostName'] = row[0]
                    browse['memberName'] = row[1]
                    browse['memberAvatar'] = row[2]
                    
                # 工作经历    
                sql = " select post_name AS postName, company_name AS companyName,id AS id from wms_work_exp where member_id = %s " \
                      "    order by id desc LIMIT 1 "
                cur.execute(sql,(browse['memberId']))
                for row in cur.fetchall():
                    browse['companyPostName'] = row[0]
                    browse['companyName'] = row[1]

                # 期望薪资
                sql = " select expect_salary AS expectSalary, id AS id from wms_job_expect  where member_id = %s order by id desc LIMIT 1 "
                cur.execute(sql,(browse['memberId']))
                for row in cur.fetchall():            
                    browse['expectSalary'] = row[0]

                # 应聘岗位名称
                #sql = " select post_name AS postName from rms_company_position where 1 = 1 and id = %s "
                #cur.execute(sql,(collect['positionId']))
                #for row in cur.fetchall():
                #    collect['postName'] = row[0]

                # 是否已读
                ifRead = False
                sql = " select apply_id from wms_apply_read where 1 = 1 and apply_id = %s "
                cur.execute(sql,(browse['id']))
                for row in cur.fetchall():
                    ifRead = True
                browse['ifRead'] = ifRead
            browselist.append(browse)

    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return browselist    

# 删除收藏
def deleteCollect(param):
    conn = connect_db()
    cur = conn.cursor()
    cnt = 0
    try:
        sql = " delete from wms_collect where id = %s "
        cnt = cur.execute(sql,(param['id']))
        conn.commit()
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return cnt

# 新增/更新收藏信息
def addOrUpdateCollect(param):
    conn = connect_db()
    cur = conn.cursor()
    cnt = 0
    try:
        if param['type'] == 1:
            sql = " select id from wms_collect where 1 = 1 AND member_id = %s AND resume_id = %s "  
            cnt = cur.execute(sql,(param['memberId'],param['resumeId']))
            if cnt == 0:
                sql = " insert into wms_collect ( type, member_id, com_member_id, resume_id, create_time ) values (%s, %s, %s, %s, %s)"
                cnt = cur.execute(sql,(param['type'],param['memberId'],param['comMemberId'],param['resumeId'],datetime.datetime.now()))
            else:
                sql = " delete from wms_collect where 1 = 1 AND member_id = %s AND resume_id = %s "  
                cnt = cur.execute(sql,(param['memberId'],param['resumeId']))
        else:
            sql = " select id from wms_collect where 1 = 1 AND member_id = %s AND position_id = %s "  
            cnt = cur.execute(sql,(param['memberId'],param['positionId']))
            if cnt == 0:
                sql = " insert into wms_collect ( type, member_id, com_member_id, position_id, company_id, create_time ) values (%s, %s, %s, %s, %s, %s)"
                cnt = cur.execute(sql,(param['type'],param['memberId'],param['comMemberId'],param['positionId'],param['companyId'],datetime.datetime.now()))
            else:
                sql = " delete from wms_collect where 1 = 1 AND member_id = %s AND position_id = %s "  
                cnt = cur.execute(sql,(param['memberId'],param['positionId']))
        conn.commit()
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return cnt

# 新增/更新职位申请
def addOrUpdateApply(param):
    conn = connect_db()
    cur = conn.cursor()
    cnt = 0
    try:
        sql = "update wms_apply_log set type=%s, member_id=%s, com_member_id=%s, position_id=%s, tcode=%s, company_id=%s, update_time=%s where 1 = 1 and member_id = %s and position_id = %s "
        cnt = cur.execute(sql,(0,param['memberId'],param['comMemberId'],param['positionId'],param['tcode'],param['companyId'],datetime.datetime.now()
                               ,param['memberId'],param['positionId']))
        if cnt == 0:
            sql = "insert into wms_apply_log ( type, member_id, com_member_id, position_id, tcode, company_id, create_time ) values (%s, %s, %s, %s, %s, %s, %s)"
            cnt = cur.execute(sql,(0,param['memberId'],param['comMemberId'],param['positionId'],param['tcode'],param['companyId'],datetime.datetime.now()))
        conn.commit()
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return cnt

# 新增/更新分享记录
def addOrUpdateShare(param):
    conn = connect_db()
    cur = conn.cursor()
    cnt = 0
    try:
        sql = "update ums_share_log set type=%s, member_id=%s, com_member_id=%s, data_id=%s, company_id=%s, update_time=%s where 1 = 1 and member_id = %s and type = %s and data_id = %s "
        cnt = cur.execute(sql,(param['type'],param['memberId'],param['comMemberId'],param['dataId'],param['companyId'],datetime.datetime.now()
                               ,param['memberId'],param['type'],param['dataId']))
        if cnt == 0:
            sql = "insert into ums_share_log ( type, member_id, com_member_id, data_id, company_id, create_time ) values (%s, %s, %s, %s, %s, %s)"
            cnt = cur.execute(sql,(param['type'],param['memberId'],param['comMemberId'],param['dataId'],param['companyId'],datetime.datetime.now()))
        conn.commit()
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return cnt

# 查询收藏列表
def getCollectList(param):
    conn = connect_db()
    cur = conn.cursor()
    collectlist = []
    try:
        clist = []
        sql = ""
        if param['type'] == '0':
            sql = " select id AS id, type AS type, member_id AS memberId,com_member_id AS comMemberId,  position_id AS positionId, company_id AS companyId, resume_id AS resumeId " \
                  ", status AS status, remark AS remark from wms_collect where 1 = 1 and member_id = %s and type = 0 order by create_time desc "
        else:
            sql = " select id AS id, type AS type, member_id AS memberId,com_member_id AS comMemberId,  position_id AS positionId, company_id AS companyId, resume_id AS resumeId " \
                  ", status AS status, remark AS remark from wms_collect where 1 = 1 and com_member_id = %s and type = 1 order by create_time desc "
        cur.execute(sql,(param['memberId']))
        for row in cur.fetchall():
            collect = {}
            collect['id'] = row[0]
            collect['type'] = row[1]
            collect['memberId'] = row[2]
            collect['comMemberId'] = row[3]
            collect['positionId'] = row[4]
            collect['companyId'] = row[5]
            collect['resumeId'] = row[6]
            collect['status'] = row[7]
            collect['remark'] = row[8]
            clist.append(collect)
        for collect in clist:
            if collect['positionId'] is not None:
                # 职位信息
                sql = " select id AS id, company_id AS companyId,browse_number AS browseNumber,skill AS skill,if_parttime AS ifParttime,salary AS salary,address_name AS addressName,address_house AS addressHouse " \
                      "   , post_id AS postId, post_name AS postName, descr AS descr, address AS address, longitude AS longitude, latitude AS latitude, exp_require AS expRequire, min_education AS minEducation " \
                      "   , salary_low AS salaryLow, salary_up AS salaryUp, salary_month AS salaryMonth, unit AS unit, bonus AS bonus, settlement_type AS settlementType, recruits_number AS recruitsNumber " \
                      "   , part_date AS partDate, part_time AS partTime, phone AS phone, position_type AS positionType, start_date AS startDate, end_date AS endDate, work_days AS workDays, share_number AS shareNumber " \
                      "   ,share_money AS shareMoney, city AS city, citycode AS citycode, pcity AS pcity, pcitycode AS pcitycode, status AS status, remark AS remark from rms_company_position where 1 = 1 AND id = %s  "
                cur.execute(sql,(collect['positionId']))
                for row in cur.fetchall():
                    position = {}
                    position['id'] = row[0]
                    position['companyId'] = row[1]
                    position['browseNumber'] = row[2]
                    position['skill'] = row[3]
                    position['ifParttime'] = row[4]
                    position['salary'] = row[5]
                    position['addressName'] = row[6]
                    position['addressHouse'] = row[7]
                    position['postId'] = row[8]
                    position['postName'] = row[9]
                    position['descr'] = row[10]
                    position['address'] = row[11]
                    position['longitude'] = row[12]
                    position['latitude'] = row[13]
                    position['expRequire'] = row[14]
                    position['minEducation'] = row[15]
                    position['salaryLow'] = row[16]
                    position['salaryUp'] = row[17]
                    position['salaryMonth'] = row[18]
                    position['unit'] = row[19]
                    position['bonus'] = row[20]
                    position['settlementType'] = row[21]
                    position['recruitsNumber'] = row[22]
                    position['partDate'] = row[23]
                    position['partTime'] = row[24]
                    position['phone'] = row[25]
                    position['positionType'] = row[26]
                    position['startDate'] = row[27]
                    position['endDate'] = row[28]
                    position['workDays'] = row[29]
                    position['shareNumber'] = row[30]
                    position['shareMoney'] = row[31]
                    position['city'] = row[32]
                    position['citycode'] = row[33]
                    position['pcity'] = row[34]
                    position['pcitycode'] = row[35]
                    position['status'] = row[36]
                    position['remark'] = row[37]
                collect['position'] = position  
                # 招聘者信息
                sql = " select post_name AS postName, name AS name, avatar AS avatar from ums_member where if_del = 0 and member_id = %s LIMIT 1  "
                cur.execute(sql,(collect['comMemberId']))
                for row in cur.fetchall():
                    collect['memberPostName'] = row[0]
                    collect['memberName'] = row[1]
                    collect['memberAvatar'] = row[2]
                    
                # 企业信息
                sql = " select name,staff_size,nature from rms_company where id=%s   "
                cur.execute(sql,(collect['companyId']))                
                for row in cur.fetchall():
                    collect['companyName'] = row[0]
                    collect['companyStaffSize'] = row[1]
                    collect['companyNature'] = row[2]
                    
            if collect['resumeId'] is not None:
                # 求职者简历信息
                sql = " select id AS id, name AS name, sex AS sex,email AS email,browse_number AS browseNumber,skills AS skills,member_id AS memberId, phone AS phone, avatar AS avatar, education AS education " \
                      "   , time_to_work AS timeToWork, work_year AS workYear, birthday AS birthday, salary_low AS salaryLow, salary_up AS salaryUp, company AS company, post_id AS postId " \
                      "   , personal_skill AS personalSkill, job_status AS jobStatus, status AS status, remark AS remark " \
                      " from wms_resume where 1 = 1 and member_id = %s " 
                cur.execute(sql,(collect['memberId']))
                for row in cur.fetchall():
                    resume = {}
                    resume['id'] = row[0]
                    resume['name'] = row[1]
                    resume['sex'] = row[2]
                    resume['email'] = row[3]
                    resume['browseNumber'] = row[4]
                    resume['skills'] = row[5]
                    resume['memberId'] = row[6]
                    resume['phone'] = row[7]
                    resume['avatar'] = row[8]
                    resume['education'] = row[9]
                    resume['timeToWork'] = row[10]
                    resume['workYear'] = row[11]
                    resume['birthday'] = row[12]
                    resume['salaryLow'] = row[13]
                    resume['salaryUp'] = row[14]
                    resume['company'] = row[15]
                    resume['postId'] = row[16]
                    resume['personalSkill'] = row[17]
                    resume['jobStatus'] = row[18]
                    resume['status'] = row[19]
                    resume['remark'] = row[20]
                    collect['resume'] = resume

                # 求职者信息
                sql = " select post_name AS postName, name AS name, avatar AS avatar from ums_member where if_del = 0 and member_id = %s LIMIT 1  "
                cur.execute(sql,(collect['memberId']))
                for row in cur.fetchall():
                    collect['memberPostName'] = row[0]
                    collect['memberName'] = row[1]
                    collect['memberAvatar'] = row[2]
                    
                # 工作经历    
                sql = " select post_name AS postName, company_name AS companyName,id AS id from wms_work_exp where member_id = %s " \
                      "    order by id desc LIMIT 1 "
                cur.execute(sql,(collect['memberId']))
                for row in cur.fetchall():
                    collect['companyPostName'] = row[0]
                    collect['companyName'] = row[1]

                # 期望薪资
                sql = " select expect_salary AS expectSalary, id AS id from wms_job_expect  where member_id = %s order by id desc LIMIT 1 "
                cur.execute(sql,(collect['memberId']))
                for row in cur.fetchall():            
                    collect['expectSalary'] = row[0]

                # 应聘岗位名称
                #sql = " select post_name AS postName from rms_company_position where 1 = 1 and id = %s "
                #cur.execute(sql,(collect['positionId']))
                #for row in cur.fetchall():
                #    collect['postName'] = row[0]

                # 是否已读
                ifRead = False
                sql = " select apply_id from wms_apply_read where 1 = 1 and apply_id = %s "
                cur.execute(sql,(collect['id']))
                for row in cur.fetchall():
                    ifRead = True
                collect['ifRead'] = ifRead
            collectlist.append(collect)

    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return collectlist

# 查询沟通记录列表
def getCommList(param):
    conn = connect_db()
    cur = conn.cursor()
    commlist = []
    try:
        clist = []
        sql = ""
        if param['type'] == '0':
            sql = " select id AS id, type AS type, member_id AS memberId,com_member_id AS comMemberId,  position_id AS positionId, company_id AS companyId, resume_id AS resumeId " \
                  ", status AS status, remark AS remark from from wms_communicate where 1 = 1 and member_id = %s order by create_time desc "
        else:
            sql = " select id AS id, type AS type, member_id AS memberId,com_member_id AS comMemberId,  position_id AS positionId, company_id AS companyId, resume_id AS resumeId " \
                  ", status AS status, remark AS remark from wms_communicate where 1 = 1 and com_member_id = %s order by create_time desc "

        cur.execute(sql,(param['memberId']))
        for row in cur.fetchall():
            comm = {}
            comm['id'] = row[0]
            comm['type'] = row[1]
            comm['memberId'] = row[2]
            comm['comMemberId'] = row[3]
            comm['positionId'] = row[4]
            comm['companyId'] = row[5]
            comm['resumeId'] = row[6]
            comm['status'] = row[7]
            comm['remark'] = row[8]
            clist.append(comm)
        for comm in clist:
            if comm['positionId'] is not None:
                # 职位信息
                sql = " select id AS id, company_id AS companyId,browse_number AS browseNumber,skill AS skill,if_parttime AS ifParttime,salary AS salary,address_name AS addressName,address_house AS addressHouse " \
                      "   , post_id AS postId, post_name AS postName, descr AS descr, address AS address, longitude AS longitude, latitude AS latitude, exp_require AS expRequire, min_education AS minEducation " \
                      "   , salary_low AS salaryLow, salary_up AS salaryUp, salary_month AS salaryMonth, unit AS unit, bonus AS bonus, settlement_type AS settlementType, recruits_number AS recruitsNumber " \
                      "   , part_date AS partDate, part_time AS partTime, phone AS phone, position_type AS positionType, start_date AS startDate, end_date AS endDate, work_days AS workDays, share_number AS shareNumber " \
                      "   ,share_money AS shareMoney, city AS city, citycode AS citycode, pcity AS pcity, pcitycode AS pcitycode, status AS status, remark AS remark from rms_company_position where 1 = 1 AND id = %s  "
                cur.execute(sql,(comm['positionId']))
                for row in cur.fetchall():
                    position = {}
                    position['id'] = row[0]
                    position['companyId'] = row[1]
                    position['browseNumber'] = row[2]
                    position['skill'] = row[3]
                    position['ifParttime'] = row[4]
                    position['salary'] = row[5]
                    position['addressName'] = row[6]
                    position['addressHouse'] = row[7]
                    position['postId'] = row[8]
                    position['postName'] = row[9]
                    position['descr'] = row[10]
                    position['address'] = row[11]
                    position['longitude'] = row[12]
                    position['latitude'] = row[13]
                    position['expRequire'] = row[14]
                    position['minEducation'] = row[15]
                    position['salaryLow'] = row[16]
                    position['salaryUp'] = row[17]
                    position['salaryMonth'] = row[18]
                    position['unit'] = row[19]
                    position['bonus'] = row[20]
                    position['settlementType'] = row[21]
                    position['recruitsNumber'] = row[22]
                    position['partDate'] = row[23]
                    position['partTime'] = row[24]
                    position['phone'] = row[25]
                    position['positionType'] = row[26]
                    position['startDate'] = row[27]
                    position['endDate'] = row[28]
                    position['workDays'] = row[29]
                    position['shareNumber'] = row[30]
                    position['shareMoney'] = row[31]
                    position['city'] = row[32]
                    position['citycode'] = row[33]
                    position['pcity'] = row[34]
                    position['pcitycode'] = row[35]
                    position['status'] = row[36]
                    position['remark'] = row[37]
                comm['position'] = position  
                # 招聘者信息
                sql = " select post_name AS postName, name AS name, avatar AS avatar from ums_member where if_del = 0 and member_id = %s LIMIT 1  "
                cur.execute(sql,(comm['comMemberId']))
                for row in cur.fetchall():
                    comm['memberPostName'] = row[0]
                    comm['memberName'] = row[1]
                    comm['memberAvatar'] = row[2]
                    
                # 企业信息
                sql = " select name,staff_size,nature from rms_company where id=%s   "
                cur.execute(sql,(comm['companyId']))                
                for row in cur.fetchall():
                    comm['companyName'] = row[0]
                    comm['companyStaffSize'] = row[1]
                    comm['companyNature'] = row[2]
                    
            if comm['resumeId'] is not None:
                # 求职者简历信息
                sql = " select id AS id, name AS name, sex AS sex,email AS email,browse_number AS browseNumber,skills AS skills,member_id AS memberId, phone AS phone, avatar AS avatar, education AS education " \
                      "   , time_to_work AS timeToWork, work_year AS workYear, birthday AS birthday, salary_low AS salaryLow, salary_up AS salaryUp, company AS company, post_id AS postId " \
                      "   , personal_skill AS personalSkill, job_status AS jobStatus, status AS status, remark AS remark " \
                      " from wms_resume where 1 = 1 and member_id = %s " 
                cur.execute(sql,(comm['memberId']))
                for row in cur.fetchall():
                    resume = {}
                    resume['id'] = row[0]
                    resume['name'] = row[1]
                    resume['sex'] = row[2]
                    resume['email'] = row[3]
                    resume['browseNumber'] = row[4]
                    resume['skills'] = row[5]
                    resume['memberId'] = row[6]
                    resume['phone'] = row[7]
                    resume['avatar'] = row[8]
                    resume['education'] = row[9]
                    resume['timeToWork'] = row[10]
                    resume['workYear'] = row[11]
                    resume['birthday'] = row[12]
                    resume['salaryLow'] = row[13]
                    resume['salaryUp'] = row[14]
                    resume['company'] = row[15]
                    resume['postId'] = row[16]
                    resume['personalSkill'] = row[17]
                    resume['jobStatus'] = row[18]
                    resume['status'] = row[19]
                    resume['remark'] = row[20]
                    comm['resume'] = resume

                # 求职者信息
                sql = " select post_name AS postName, name AS name, avatar AS avatar from ums_member where if_del = 0 and member_id = %s LIMIT 1  "
                cur.execute(sql,(comm['memberId']))
                for row in cur.fetchall():
                    comm['memberPostName'] = row[0]
                    comm['memberName'] = row[1]
                    comm['memberAvatar'] = row[2]
                    
                # 工作经历    
                sql = " select post_name AS postName, company_name AS companyName,id AS id from wms_work_exp where member_id = %s " \
                      "    order by id desc LIMIT 1 "
                cur.execute(sql,(comm['memberId']))
                for row in cur.fetchall():
                    comm['companyPostName'] = row[0]
                    comm['companyName'] = row[1]

                # 期望薪资
                sql = " select expect_salary AS expectSalary, id AS id from wms_job_expect  where member_id = %s order by id desc LIMIT 1 "
                cur.execute(sql,(comm['memberId']))
                for row in cur.fetchall():            
                    comm['expectSalary'] = row[0]

                # 应聘岗位名称
                #sql = " select post_name AS postName from rms_company_position where 1 = 1 and id = %s "
                #cur.execute(sql,(collect['positionId']))
                #for row in cur.fetchall():
                #    collect['postName'] = row[0]

                # 是否已读
                ifRead = False
                sql = " select apply_id from wms_apply_read where 1 = 1 and apply_id = %s "
                cur.execute(sql,(comm['id']))
                for row in cur.fetchall():
                    ifRead = True
                comm['ifRead'] = ifRead
            commlist.append(comm)

    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return commlist

# 查询沟通记录详情
def getCommDetail(param):
    conn = connect_db()
    cur = conn.cursor()
    comm = {}
    try:
        sql = " select id AS id, type AS type, member_id AS memberId,com_member_id AS comMemberId,  position_id AS positionId, company_id AS companyId, resume_id AS resumeId " \
              ", status AS status, remark AS remark from wms_communicate where 1 = 1 AND member_id = %s AND com_member_id = %s "

        cur.execute(sql,(param['memberId'],param['comMemberId']))
        for row in cur.fetchall():
            comm['id'] = row[0]
            comm['type'] = row[1]
            comm['memberId'] = row[2]
            comm['comMemberId'] = row[3]
            comm['positionId'] = row[4]
            comm['companyId'] = row[5]
            comm['resumeId'] = row[6]
            comm['status'] = row[7]
            comm['remark'] = row[8]
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return comm

# 新增/更新沟通记录
def addOrUpdateComm(param):
    conn = connect_db()
    cur = conn.cursor()
    cnt = 0
    try:
        sql = "update wms_communicate set position_id=%s,company_id=%s, update_time=%s where 1 = 1 and member_id = %s and com_member_id = %s "
        cnt = cur.execute(sql,(param['positionId'],param['companyId'],datetime.datetime.now(),param['memberId'],param['comMemberId']))
        if cnt == 0:
            sql = "insert into wms_communicate ( member_id, com_member_id, position_id, company_id, create_time ) values (%s, %s, %s, %s, %s)"
            cnt = cur.execute(sql,(0,param['memberId'],param['comMemberId'],param['positionId'],param['companyId'],datetime.datetime.now()))
        conn.commit()
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return cnt

# 查询看过我列表
def getVisitList(param):
    conn = connect_db()
    cur = conn.cursor()
    visitlist = []
    try:
        clist = []
        sql = ""
        if param['type'] == '1':
            sql = " select id AS id, type AS type, member_id AS memberId, tcode AS tcode,com_member_id AS comMemberId, position_id AS positionId, company_id AS companyId " \
                  ", resume_id AS resumeId, status AS status, remark AS remark from wms_browse where 1 = 1 AND com_member_id = %s AND type = %s order by create_time desc "
            cur.execute(sql,(param['comMemberId'],0))
        else:
            sql = " select id AS id, type AS type, member_id AS memberId, tcode AS tcode,com_member_id AS comMemberId, position_id AS positionId, company_id AS companyId " \
                  ", resume_id AS resumeId, status AS status, remark AS remark from wms_browse where 1 = 1 AND member_id = %s AND type = %s order by create_time desc "
            cur.execute(sql,(param['memberId'],1))
        for row in cur.fetchall():
            collect = {}
            collect['id'] = row[0]
            collect['type'] = row[1]
            collect['memberId'] = row[2]
            collect['tcode'] = row[3]
            collect['comMemberId'] = row[4]
            collect['positionId'] = row[5]
            collect['companyId'] = row[6]
            collect['resumeId'] = row[7]
            collect['status'] = row[8]
            collect['remark'] = row[9]
            clist.append(collect)

        for collect in clist:
            if collect['type'] == 1:
                # 企业信息
                sql = " select logo,name,industry_name,staff_size,start_time,end_time,rest_time,id from rms_company where id=%s   "
                cur.execute(sql,(collect['companyId']))                
                for row in cur.fetchall():
                    company = {}                   
                    company['logo'] = row[0]
                    company['name'] = row[1]
                    company['industryName'] = row[2]
                    company['staffSize'] = row[3]
                    company['startTime'] = row[4]
                    company['endTime'] = row[5]
                    company['restTime'] = row[6]
                    company['id'] = row[7]
                    collect['company'] = company
                    
            if collect['type'] == 0:
                # 求职者简历信息
                sql = " select id AS id, name AS name, sex AS sex,email AS email,browse_number AS browseNumber,skills AS skills,member_id AS memberId, phone AS phone, avatar AS avatar, education AS education " \
                      "   , time_to_work AS timeToWork, work_year AS workYear, birthday AS birthday, salary_low AS salaryLow, salary_up AS salaryUp, company AS company, post_id AS postId " \
                      "   , personal_skill AS personalSkill, job_status AS jobStatus, status AS status, remark AS remark " \
                      " from wms_resume where 1 = 1 and member_id = %s " 
                cur.execute(sql,(collect['memberId']))
                for row in cur.fetchall():
                    resume = {}
                    resume['id'] = row[0]
                    resume['name'] = row[1]
                    resume['sex'] = row[2]
                    resume['email'] = row[3]
                    resume['browseNumber'] = row[4]
                    resume['skills'] = row[5]
                    resume['memberId'] = row[6]
                    resume['phone'] = row[7]
                    resume['avatar'] = row[8]
                    resume['education'] = row[9]
                    resume['timeToWork'] = row[10]
                    resume['workYear'] = row[11]
                    resume['birthday'] = row[12]
                    resume['salaryLow'] = row[13]
                    resume['salaryUp'] = row[14]
                    resume['company'] = row[15]
                    resume['postId'] = row[16]
                    resume['personalSkill'] = row[17]
                    resume['jobStatus'] = row[18]
                    resume['status'] = row[19]
                    resume['remark'] = row[20]
                    collect['resume'] = resume

                # 求职者信息
                sql = " select post_name AS postName, name AS name, avatar AS avatar from ums_member where if_del = 0 and member_id = %s LIMIT 1  "
                cur.execute(sql,(collect['memberId']))
                for row in cur.fetchall():
                    collect['memberPostName'] = row[0]
                    collect['memberName'] = row[1]
                    collect['memberAvatar'] = row[2]
                    
                # 工作经历    
                sql = " select post_name AS postName, company_name AS companyName from wms_work_exp where member_id = %s " \
                      "    order by id desc LIMIT 1 "
                cur.execute(sql,(collect['memberId']))
                for row in cur.fetchall():
                    collect['companyPostName'] = row[0]
                    collect['companyName'] = row[1]

                # 期望薪资
                sql = " select expect_salary AS expectSalary from wms_job_expect  where member_id = %s order by id desc LIMIT 1 "
                cur.execute(sql,(collect['memberId']))
                for row in cur.fetchall():            
                    collect['expectSalary'] = row[0]

                # 应聘岗位名称
                sql = " select post_name AS postName from rms_company_position where 1 = 1 and id = %s "
                cur.execute(sql,(collect['positionId']))
                for row in cur.fetchall():
                    collect['postName'] = row[0]

                # 是否已读
                ifRead = False
                sql = " select apply_id from wms_apply_read where 1 = 1 and apply_id = %s "
                cur.execute(sql,(collect['id']))
                for row in cur.fetchall():
                    ifRead = True
                collect['ifRead'] = ifRead
            visitlist.append(collect)
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return visitlist

# 查询简历信息
def wmsgetResumeList(param):
    conn = connect_db()
    cur = conn.cursor()
    resumelist = []
    try:
        sql = " select a.id,a.member_id,a.name,a.phone,a.avatar,a.sex,a.education,a.time_to_work,a.birthday,a.personal_skill,a.skills,a.job_status,a.status,b.post_id " \
                 "   ,b.salary,c.company_name,c.industry_name,c.post_name,c.department,c.skill " \
                 " from wms_resume a " \
                 " LEFT JOIN " \
                 " ( " \
                 "   SELECT post_id,post_name,industry_name,expect_salary as salary ,member_id FROM wms_job_expect td,(SELECT max(id) as id FROM wms_job_expect GROUP BY member_id) md  where td.id = md.id "\
                 " ) b on b.member_id = a.member_id " \
                 " LEFT JOIN " \
                 " ( " \
                 " SELECT member_id,company_name,industry_name,post_name,department,skill FROM wms_work_exp td,(SELECT max(id) as id FROM wms_work_exp GROUP BY member_id) md  where td.id = md.id " \
                 " ) c on c.member_id = a.member_id " \
                 " where 1 = 1 "
        if 'id' in param.keys():
            sql += " and a.id != " + param['id']
        if 'keyword' in param.keys():
            sql += " and (a.name like '%" + param['keyword'] + "%' or b.post_name like '%" + param['keyword'] + "%' or c.company_name like '%" + param['keyword'] + "%') "
        if 'postId' in param.keys():
            sql += " and b.post_id = " + param['postId']
        if 'education' in param.keys():
            sql += " and a.education = " + param['education']        
        if 'salary' in param.keys():
            sql += " and b.salary = '" + param['salary'] + "' "
        sql += " order by id desc "
            
        cur.execute(sql)
        for row in cur.fetchall():
            rowdict = {}
            rowdict['id'] = row[0]
            rowdict['member_id'] = row[1]
            rowdict['name'] = row[2]
            rowdict['phone'] = row[3]
            rowdict['avatar'] = row[4]
            rowdict['sex'] = row[5]
            rowdict['education'] = row[6]

            rowdict['timeToWork'] = row[7]
            rowdict['birthday'] = row[8]
            rowdict['personal_skill'] = row[9]
            rowdict['skills'] = row[10]
            rowdict['job_status'] = row[11]
            rowdict['status'] = row[12]
            rowdict['post_id'] = row[13]

            rowdict['salary'] = row[14]
            rowdict['companyName'] = row[15]
            rowdict['industry_name'] = row[16]
            rowdict['postName'] = row[17]
            rowdict['department'] = row[18]
            rowdict['skill'] = row[19]

            resumelist.append(rowdict)

    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return resumelist

# 查询简历完整信息
def wmsgetResume(param):
    conn = connect_db()
    cur = conn.cursor()
    resume = {}
    try:         
        sql = " select id AS id, name AS name, sex AS sex,email AS email,browse_number AS browseNumber,skills AS skills,member_id AS memberId, phone AS phone, avatar AS avatar, education AS education " \
              "   , time_to_work AS timeToWork, work_year AS workYear, birthday AS birthday, salary_low AS salaryLow, salary_up AS salaryUp, company AS company, post_id AS postId " \
              "   , personal_skill AS personalSkill, job_status AS jobStatus, status AS status, remark AS remark " \
              " from wms_resume where 1 = 1 " 
        if param['id'] is not None:
            sql += " and id = " + str(param['id'])
        else:
            if param['memberId'] is not None:
                sql += " and member_id = " + str(param['memberId'])
            
        cur.execute(sql)
        for row in cur.fetchall():
            resume['id'] = row[0]
            resume['name'] = row[1]
            resume['sex'] = row[2]
            resume['email'] = row[3]
            resume['browseNumber'] = row[4]
            resume['skills'] = row[5]
            resume['memberId'] = row[6]
            resume['phone'] = row[7]
            resume['avatar'] = row[8]
            resume['education'] = row[9]
            resume['timeToWork'] = row[10]
            resume['workYear'] = row[11]
            resume['birthday'] = row[12]
            resume['salaryLow'] = row[13]
            resume['salaryUp'] = row[14]
            resume['company'] = row[15]
            resume['postId'] = row[16]
            resume['personalSkill'] = row[17]
            resume['jobStatus'] = row[18]
            resume['status'] = row[19]
            resume['remark'] = row[20]

            # 求职期望
            jobexplist = []
            sql = " select id AS id, member_id AS memberId,post_name AS postName,industry_name AS industryName,expect_salary AS expectSalary, job_type AS jobType, work_city AS workCity, post_id AS postId " \
                  "    , industry_id AS industryId, salary_low AS salaryLow, salary_up AS salaryUp, sort AS sort, status AS status, remark AS remark from wms_job_expect  where member_id = %s order by id desc  "
            cur.execute(sql,(resume['memberId']))
            for row in cur.fetchall():            
                job = {}
                job['id'] = row[0]
                job['memberId'] = row[1]
                job['postName'] = row[2]
                job['industryName'] = row[3]
                job['expectSalary'] = row[4]
                job['jobType'] = row[5]
                job['workCity'] = row[6]
                job['postId'] = row[7]
                job['industryId'] = row[8]
                job['salaryLow'] = row[9]
                job['salaryUp'] = row[10]
                job['sort'] = row[11]
                job['status'] = row[12]
                job['remark'] = row[13]
                jobexplist.append(job)
            resume['jobExpectList'] = jobexplist

            # 工作经历
            workexplist = []
            sql = " select id AS id, member_id AS memberId,post_name AS postName,skill AS skill,industry_name AS industryName, company_name AS companyName, industry_id AS industryId, start_date AS startDate " \
                  "    , end_date AS endDate, post_id AS postId, job_content AS jobContent, department AS department, status AS status, remark AS remark from wms_work_exp where member_id = %s " \
                  "    order by id desc "
            cur.execute(sql,(resume['memberId']))
            for row in cur.fetchall():
                work = {}
                work['id'] = row[0]
                work['memberId'] = row[1]
                work['postName'] = row[2]
                work['skill'] = row[3]
                work['industryName'] = row[4]
                work['companyName'] = row[5]
                work['industryId'] = row[6]
                work['startDate'] = row[7]
                work['endDate'] = row[8]
                work['postId'] = row[9]
                work['jobContent'] = row[10]
                work['department'] = row[11]
                work['status'] = row[12]
                work['remark'] = row[13]
                workexplist.append(work)
            resume['workExpList'] = workexplist
            # 项目经历
            proexplist = []
            sql = " select id AS id, member_id AS memberId, name AS name, role AS role, start_date AS startDate, end_date AS endDate, descr AS descr, performance AS performance, url AS url, status AS status " \
                  "    , remark AS remark from wms_pro_exp where member_id = %s  "
            cur.execute(sql,(resume['memberId']))
            for row in cur.fetchall():
                project = {}
                project['id'] = row[0]
                project['memberId'] = row[1]
                project['name'] = row[2]
                project['role'] = row[3]
                project['startDate'] = row[4]
                project['endDate'] = row[5]
                project['descr'] = row[6]
                project['performance'] = row[7]
                project['url'] = row[8]
                project['status'] = row[9]
                project['remark'] = row[10]
                proexplist.append(project)
            resume['proExpList'] = proexplist
            # 教育经历
            eduexplist = []
            sql = " select id AS id, member_id AS memberId, school AS school, education AS education, if_full_time AS ifFullTime, major AS major, start_time AS startTime, end_time AS endTime, school_exp AS schoolExp " \
                  "    , status AS status, remark AS remark from wms_edu_exp where member_id = %s "
            cur.execute(sql,(resume['memberId']))
            for row in cur.fetchall():
                education = {}
                education['id'] = row[0]
                education['memberId'] = row[1]
                education['school'] = row[2]
                education['education'] = row[3]
                education['ifFullTime'] = row[4]
                education['major'] = row[5]
                education['startTime'] = row[6]
                education['endTime'] = row[7]
                education['schoolExp'] = row[8]
                education['status'] = row[9]
                education['remark'] = row[10]
                eduexplist.append(education)
            resume['eduExpList'] = eduexplist
            # 会员编码
            resume['memberSn'] = ''
            sql = " select member_sn from ums_member WHERE member_id = %s "
            cur.execute(sql,(resume['memberId']))
            for row in cur.fetchall():
                resume['memberSn'] = row[0]
        ifCollect = False
        sql = " select resume_id from wms_collect where com_member_id = %s and resume_id = %s "
        cur.execute(sql,(param['memberId'],resume['id']))
        for row in cur.fetchall():
            ifCollect = True
        resume['ifCollect'] = ifCollect
        logger.info(resume)
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return resume

# 查询简历基本信息
def getResumeInfo(param):
    conn = connect_db()
    cur = conn.cursor()
    resume = {}
    try:         
        sql = " select id AS id, name AS name, sex AS sex,email AS email,browse_number AS browseNumber,skills AS skills,member_id AS memberId, phone AS phone, avatar AS avatar, education AS education " \
              "   , DATE_FORMAT(time_to_work, '%%Y-%%m-%%d') AS timeToWork, work_year AS workYear, DATE_FORMAT(birthday, '%%Y-%%m-%%d') AS birthday, salary_low AS salaryLow, salary_up AS salaryUp, company AS company, post_id AS postId " \
              "   , personal_skill AS personalSkill, job_status AS jobStatus, status AS status, remark AS remark " \
              " from wms_resume where 1 = 1 and member_id = %s " 
           
        cur.execute(sql,(param['memberId']))
        for row in cur.fetchall():
            resume['id'] = row[0]
            resume['name'] = row[1]
            resume['sex'] = row[2]
            resume['email'] = row[3]
            resume['browseNumber'] = row[4]
            resume['skills'] = row[5]
            resume['memberId'] = row[6]
            resume['phone'] = row[7]
            resume['avatar'] = row[8]
            resume['education'] = row[9]
            resume['timeToWork'] = row[10]
            resume['workYear'] = row[11]
            resume['birthday'] = row[12]
            resume['salaryLow'] = row[13]
            resume['salaryUp'] = row[14]
            resume['company'] = row[15]
            resume['postId'] = row[16]
            resume['personalSkill'] = row[17]
            resume['jobStatus'] = row[18]
            resume['status'] = row[19]
            resume['remark'] = row[20]

    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return resume

# 更新简历基本信息
def updateResumeBasic(param):
    conn = connect_db()
    cur = conn.cursor()
    cnt = 0
    try:
        if 'jobStatus' in param.keys():
            sql = "update wms_resume set job_status=%s, update_time=%s where 1 = 1 and member_id = %s and id = %s  "
            cnt = cur.execute(sql,(param['jobStatus'],datetime.datetime.now(),param['memberId'],param['id']))
            if cnt == 0:
                sql = "insert into wms_resume ( member_id, job_status, create_time )  values (%s, %s, %s)"
                cnt = cur.execute(sql,(param['memberId'],param['jobStatus'],datetime.datetime.now()))
        if 'personalSkill' in param.keys():
            sql = "update wms_resume set personal_skill=%s, update_time=%s where 1 = 1 and member_id = %s and id = %s  "
            cnt = cur.execute(sql,(param['personalSkill'],datetime.datetime.now(),param['memberId'],param['id']))
            if cnt == 0:
                sql = "insert into wms_resume ( member_id, personal_skill, create_time )  values (%s, %s, %s)"
                cnt = cur.execute(sql,(param['memberId'],param['personalSkill'],datetime.datetime.now()))
        conn.commit()
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return cnt

# 更新简历基本信息(详细)
def updateResumeBasicInfo(param):
    conn = connect_db()
    cur = conn.cursor()
    cnt = 0
    try:
        sql = "update wms_resume set name = %s, phone = %s, avatar = %s, sex = %s, education = %s, time_to_work = %s, birthday = %s, email = %s, status = %s, update_by = %s, update_time = %s " \
              "where 1 = 1 and member_id = %s "
        cnt = cur.execute(sql,(param['name'],param['phone'],param['avatar'],param['sex'],param['education'],param['timeToWork'],param['birthday'],param['email'],param['status'],param['memberId']
                                   ,datetime.datetime.now(),param['memberId']))
        if cnt == 0:
            sql = "INSERT INTO wms_resume ( member_id, name, phone, avatar, sex, education, time_to_work, birthday, email, status, create_by, create_time) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
            cnt = cur.execute(sql,(param['memberId'],param['name'],param['phone'],param['avatar'],param['sex'],param['education'],param['timeToWork'],param['birthday'],param['email'],param['status'],param['memberId']
                                   ,datetime.datetime.now()))

        sql = " update ums_member set name = %s, avatar = %s, update_time = %s where member_id = %s  "
        cnt = cur.execute(sql,(param['name'],param['avatar'],datetime.datetime.now(),param['memberId']))
        conn.commit()
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return cnt

# 查询职位申请列表
def getApplyList(param):
    conn = connect_db()
    cur = conn.cursor()
    applylist = []
    try:
        alist = []
        sql = ""
        if param['type'] == 0:
            sql = " select id AS id, type AS type, member_id AS memberId, tcode AS tcode,com_member_id AS comMemberId, position_id AS positionId " \
                  "   , company_id AS companyId, resume_id AS resumeId, status AS status, remark AS remark from wms_apply_log where 1 = 1 AND member_id = %s  order by create_time "
        else:
            sql = " select id AS id, type AS type, member_id AS memberId, tcode AS tcode,com_member_id AS comMemberId, position_id AS positionId " \
                  "   , company_id AS companyId, resume_id AS resumeId, status AS status, remark AS remark from wms_apply_log where 1 = 1 AND com_member_id = %s  order by create_time "            
        cur.execute(sql,(param['memberId']))
        for row in cur.fetchall():
            apply = {}
            apply['id'] = row[0]
            apply['type'] = row[1]
            apply['memberId'] = row[2]
            apply['tcode'] = row[3]
            apply['comMemberId'] = row[4]
            apply['positionId'] = row[5]
            apply['companyId'] = row[6]
            apply['resumeId'] = row[7]
            apply['status'] = row[8]
            apply['remark'] = row[9]
            alist.append(apply)

        for apply in alist:
            if param['type'] == '0':
                # 职位信息
                sql = " select id AS id, company_id AS companyId,browse_number AS browseNumber,skill AS skill,if_parttime AS ifParttime,salary AS salary,address_name AS addressName,address_house AS addressHouse " \
                      "   , post_id AS postId, post_name AS postName, descr AS descr, address AS address, longitude AS longitude, latitude AS latitude, exp_require AS expRequire, min_education AS minEducation " \
                      "   , salary_low AS salaryLow, salary_up AS salaryUp, salary_month AS salaryMonth, unit AS unit, bonus AS bonus, settlement_type AS settlementType, recruits_number AS recruitsNumber " \
                      "   , part_date AS partDate, part_time AS partTime, phone AS phone, position_type AS positionType, start_date AS startDate, end_date AS endDate, work_days AS workDays, share_number AS shareNumber " \
                      "   ,share_money AS shareMoney, city AS city, citycode AS citycode, pcity AS pcity, pcitycode AS pcitycode, status AS status, remark AS remark from rms_company_position where 1 = 1 AND id = %s  "
                cur.execute(sql,(apply['positionId']))
                for row in cur.fetchall():
                    position = {}
                    position['id'] = row[0]
                    position['companyId'] = row[1]
                    position['browseNumber'] = row[2]
                    position['skill'] = row[3]
                    position['ifParttime'] = row[4]
                    position['salary'] = row[5]
                    position['addressName'] = row[6]
                    position['addressHouse'] = row[7]
                    position['postId'] = row[8]
                    position['postName'] = row[9]
                    position['descr'] = row[10]
                    position['address'] = row[11]
                    position['longitude'] = row[12]
                    position['latitude'] = row[13]
                    position['expRequire'] = row[14]
                    position['minEducation'] = row[15]
                    position['salaryLow'] = row[16]
                    position['salaryUp'] = row[17]
                    position['salaryMonth'] = row[18]
                    position['unit'] = row[19]
                    position['bonus'] = row[20]
                    position['settlementType'] = row[21]
                    position['recruitsNumber'] = row[22]
                    position['partDate'] = row[23]
                    position['partTime'] = row[24]
                    position['phone'] = row[25]
                    position['positionType'] = row[26]
                    position['startDate'] = row[27]
                    position['endDate'] = row[28]
                    position['workDays'] = row[29]
                    position['shareNumber'] = row[30]
                    position['shareMoney'] = row[31]
                    position['city'] = row[32]
                    position['citycode'] = row[33]
                    position['pcity'] = row[34]
                    position['pcitycode'] = row[35]
                    position['status'] = row[36]
                    position['remark'] = row[37]
                    apply['position'] = position

                # 招聘者信息
                sql = " select post_name AS postName, name AS name, avatar AS avatar from ums_member where if_del = 0 and member_id = %s LIMIT 1  "
                cur.execute(sql,(apply['comMemberId']))
                for row in cur.fetchall():
                    apply['memberPostName'] = row[0]
                    apply['memberName'] = row[1]
                    apply['memberAvatar'] = row[2]
                    
                # 企业信息
                sql = " select name,staff_size,nature from rms_company where id=%s   "
                cur.execute(sql,(apply['companyId']))                
                for row in cur.fetchall():
                    apply['companyName'] = row[0]
                    apply['companyStaffSize'] = row[1]
                    apply['companyNature'] = row[2]
                    
            else:
                # 求职者简历信息
                sql = " select id AS id, name AS name, sex AS sex,email AS email,browse_number AS browseNumber,skills AS skills,member_id AS memberId, phone AS phone, avatar AS avatar, education AS education " \
                      "   , time_to_work AS timeToWork, work_year AS workYear, birthday AS birthday, salary_low AS salaryLow, salary_up AS salaryUp, company AS company, post_id AS postId " \
                      "   , personal_skill AS personalSkill, job_status AS jobStatus, status AS status, remark AS remark " \
                      " from wms_resume where 1 = 1 and member_id = %s " 
                cur.execute(sql,(apply['memberId']))
                for row in cur.fetchall():
                    resume = {}
                    resume['id'] = row[0]
                    resume['name'] = row[1]
                    resume['sex'] = row[2]
                    resume['email'] = row[3]
                    resume['browseNumber'] = row[4]
                    resume['skills'] = row[5]
                    resume['memberId'] = row[6]
                    resume['phone'] = row[7]
                    resume['avatar'] = row[8]
                    resume['education'] = row[9]
                    resume['timeToWork'] = row[10]
                    resume['workYear'] = row[11]
                    resume['birthday'] = row[12]
                    resume['salaryLow'] = row[13]
                    resume['salaryUp'] = row[14]
                    resume['company'] = row[15]
                    resume['postId'] = row[16]
                    resume['personalSkill'] = row[17]
                    resume['jobStatus'] = row[18]
                    resume['status'] = row[19]
                    resume['remark'] = row[20]
                    apply['resume'] = resume

                # 求职者信息
                sql = " select post_name AS postName, name AS name, avatar AS avatar from ums_member where if_del = 0 and member_id = %s LIMIT 1  "
                cur.execute(sql,(apply['memberId']))
                for row in cur.fetchall():
                    apply['memberPostName'] = row[0]
                    apply['memberName'] = row[1]
                    apply['memberAvatar'] = row[2]
                    
                # 工作经历    
                sql = " select post_name AS postName, company_name AS companyName,id AS id from wms_work_exp where member_id = %s " \
                      "    order by id desc LIMIT 1 "
                cur.execute(sql,(apply['memberId']))
                for row in cur.fetchall():
                    apply['companyPostName'] = row[0]
                    apply['companyName'] = row[1]

                # 期望薪资
                sql = " select expect_salary AS expectSalary, id AS id from wms_job_expect  where member_id = %s order by id desc LIMIT 1 "
                cur.execute(sql,(apply['memberId']))
                for row in cur.fetchall():            
                    apply['expectSalary'] = row[0]

                # 应聘岗位名称
                sql = " select post_name AS postName from rms_company_position where 1 = 1 and id = %s "
                cur.execute(sql,(apply['positionId']))
                for row in cur.fetchall():
                    apply['postName'] = row[0]

                # 是否已读
                ifRead = False
                sql = " select apply_id from wms_apply_read where 1 = 1 and apply_id = %s "
                cur.execute(sql,(apply['id']))
                for row in cur.fetchall():
                    ifRead = True
                apply['ifRead'] = ifRead
            logger.info(apply)
            applylist.append(apply)

    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return applylist

# 添加职位申请查看记录
def addApplyRead(param):
    conn = connect_db()
    cur = conn.cursor()
    cnt = 0
    try:
        sql = "update wms_apply_read set update_time=%s where 1 = 1 and member_id = %s and apply_id = %s "
        cnt = cur.execute(sql,(datetime.datetime.now(),param['memberId'],param['applyId']))
        if cnt == 0:
            sql = "insert into wms_apply_read ( apply_id, member_id, create_time ) values (%s, %s, %s)"
            cnt = cur.execute(sql,(param['applyId'],param['memberId'],datetime.datetime.now()))
        conn.commit()
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return cnt

# 删除职位申请
def deleteApply(param):
    conn = connect_db()
    cur = conn.cursor()
    cnt = 0
    try:
        sql = "delete from wms_apply_log where id = %s "
        cnt = cur.execute(sql,(param['id']))
        conn.commit()
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return cnt

# 更新简历状态
def updateApplyStatus(param):
    conn = connect_db()
    cur = conn.cursor()
    cnt = 0
    try:
        sql = "update wms_apply_log set status = %s,update_time = %s where id = %s "
        cnt = cur.execute(sql,(param['id'],datetime.datetime.now(),param['status']))
        if param['status'] == 2:
            sql = "select tcode,position_id from wms_apply_log where id = %s "
            cur.execute(sql,(param['id']))
            for row in cur.fetchall():
                if row[0] != '':
                    sql = "select share_money from rms_company_position where id = %s "
                    cur.execute(sql,(row[1]))
                    for row1 in cur.fetchall():
                        if row1[0] > 0:
                            sql = "select member_id from ums_member where if_del = 0 and member_sn = %s "
                            cur.execute(sql,(row[0]))
                            for row2 in cur.fetchall():
                                sql = "select balance,balance_withdraw from ums_member_account where 1 = 1 and member_id = %s "
                                icnt = cur.execute(sql,(row2[0]))
                                if icnt == 0:
                                    sql = " insert into ums_member_account(member_id,balance,balance_withdraw,create_time) values(%s,%s,%s,%s) "
                                    cur.execute(sql,(row2[0],row1[0],row1[0],datetime.datetime.now()))
                                else:
                                    for row3 in cur.fetchall():
                                        balance = float(row1[0]) + float(row3[0])
                                        balance_withdraw = float(row1[0]) + float(row3[1])
                                        sql = " update ums_member_account set balance = %s,balance_withdraw = %s,update_time = %s where  where 1 = 1 and member_id = %s "
                                        cur.execute(sql,(balance,balance_withdraw,datetime.datetime.now(),row2[0]))
                                sql = " insert into ums_member_account_records(member_id,type,sign,money,content,data_id,create_time) values(%s,%s,%s,%s,%s,%s,%s) "
                                cur.execute(sql,(row2[0],1,1,row1[0],"分享职位佣金收入",param['id'],datetime.now()))                                        
                    
        conn.commit()
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return cnt

# 查询期望职位详情
def getJobExpectDetail(param):
    conn = connect_db()
    cur = conn.cursor()
    results = {}
    try:         
        sql = " select job_type,post_id,post_name,expect_salary from wms_job_expect where id = %s "
        cur.execute(sql,(param['id']))
        for row in cur.fetchall():
            results['jobType'] = row[0]
            results['postId'] = row[1]
            results['postName'] = row[2]
            results['expectSalary'] = row[3]

    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return results

# 新增/更新期望职位
def updateJobExpect(param):
    conn = connect_db()
    cur = conn.cursor()
    cnt = 0
    try:
        sql = "update wms_job_expect set job_type=%s,post_id=%s,post_name=%s,industry_id=%s,industry_name=%s,expect_salary=%s,update_time=%s where member_id = %s "
        cnt = cur.execute(sql,(param['jobType'],param['postId'],param['postName'],param['industryId'],param['industryName'],param['expectSalary'],datetime.datetime.now(),param['memberId']))
        if cnt == 0:
            sql = "insert into wms_job_expect ( member_id,job_type,post_id,post_name,industry_id,industry_name,expect_salary,create_time ) values (%s, %s, %s, %s, %s, %s, %s, %s)"
            cnt = cur.execute(sql,(param['memberId'],param['jobType'],param['postId'],param['postName'],param['industryId'],param['industryName'],param['expectSalary'],datetime.datetime.now()))
        conn.commit()
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return cnt

# 删除期望职位
def deleteJobExpectById(param):
    conn = connect_db()
    cur = conn.cursor()
    cnt = 0
    try:
        sql = "delete from wms_job_expect where id = %s "
        cnt = cur.execute(sql,(param['id']))
        conn.commit()
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return cnt

# 查询工作经历详情
def getWorkExpDetail(param):
    conn = connect_db()
    cur = conn.cursor()
    results = {}
    try:         
        sql = " select industry_id as industryId,industry_name as industryName,post_id as postId,post_name as postName,start_date as startDate,end_date as endDate,job_content as jobContent,skill " \
              ",department,company_name as companyName from wms_work_exp where id = %s "
        cur.execute(sql,(param['id']))
        for row in cur.fetchall():
            results['industryId'] = row[0]
            results['industryName'] = row[1]
            results['postId'] = row[2]
            results['postName'] = row[3]
            results['startDate'] = row[4]
            results['endDate'] = row[5]
            results['jobContent'] = row[6]
            results['skill'] = row[7]
            results['department'] = row[8]
            results['companyName'] = row[9]

    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return results

# 新增/更新工作经历
def updateWorkExp(param):
    conn = connect_db()
    cur = conn.cursor()
    cnt = 0
    try:
        sql = "update wms_work_exp set member_id=%s, company_name=%s, industry_id=%s,industry_name=%s, post_name=%s, start_date=%s, end_date=%s, post_id=%s, job_content=%s, skill=%s, department=%s, update_time=%s where id=%s  "
        cnt = cur.execute(sql,(param['memberId'],param['companyName'],param['industryId'],param['industryName'],param['postName'],param['startDate'],param['endDate'],param['postId'],param['jobContent'],param['skill']
                               ,param['department'],datetime.datetime.now(),param['id']))
        if cnt == 0:
            sql = "insert into wms_work_exp ( member_id, company_name, industry_id, industry_name, post_name, start_date, end_date, post_id, job_content, skill, create_time ) walues ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s )"
            cnt = cur.execute(sql,(param['memberId'],param['companyName'],param['industryId'],param['industryName'],param['postName'],param['startDate'],param['endDate'],param['postId'],param['jobContent'],param['skill'],datetime.datetime.now()))
        conn.commit()
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return cnt

# 删除工作经历
def deleteWorkExpById(param):
    conn = connect_db()
    cur = conn.cursor()
    cnt = 0
    try:
        sql = "delete from wms_work_exp where id = %s "
        cnt = cur.execute(sql,(param['id']))
        conn.commit()
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return cnt    

# 查询项目经历详情
def getProExpDetail(param):
    conn = connect_db()
    cur = conn.cursor()
    results = {}
    try:         
        sql = " select id,name,role,start_date,end_date,descr,performance,url,status,remark from wms_pro_exp where id=%s "
        cur.execute(sql,(param['id']))
        for row in cur.fetchall():
            results['id'] = row[0]
            results['name'] = row[1]
            results['role'] = row[2]
            results['startDate'] = row[3]
            results['endDate'] = row[4]
            results['descr'] = row[5]
            results['performance'] = row[6]
            results['url'] = row[7]

    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return results

# 新增/更新项目经历
def updateProExp(param):
    conn = connect_db()
    cur = conn.cursor()
    cnt = 0
    try:
        sql = " update wms_pro_exp set member_id=%s, name=%s, role=%s, start_date=%s, end_date=%s, descr=%s, performance=%s,url=%s, update_time=%s where id=%s  "
        cnt = cur.execute(sql,(param['memberId'],param['name'],param['role'],param['startDate'],param['endDate'],param['descr'],param['performance'],param['url'],datetime.datetime.now(),param['id']))
        if cnt == 0:
            sql = " insert into wms_pro_exp ( member_id, name, role, start_date, end_date, descr, performance, url, create_time ) values ( %s, %s, %s, %s, %s, %s, %s, %s, %s )"
            cnt = cur.execute(sql,(param['memberId'],param['name'],param['role'],param['startDate'],param['endDate'],param['descr'],param['performance'],param['url'],datetime.datetime.now()))
        conn.commit()
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return cnt

# 删除项目经历
def deleteProExpById(param):
    conn = connect_db()
    cur = conn.cursor()
    cnt = 0
    try:
        sql = " delete from wms_pro_exp where id = %s "
        cnt = cur.execute(sql,(param['id']))
        conn.commit()
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return cnt 

# 查询教育经历详情
def getEduExpDetail(param):
    conn = connect_db()
    cur = conn.cursor()
    results = {}
    try:         
        sql = " select school,education,major,start_time,end_time,school_exp from wms_edu_exp where id = %s "
        cur.execute(sql,(param['id']))
        for row in cur.fetchall():
            results['school'] = row[0]
            results['education'] = row[1]
            results['major'] = row[2]
            results['startTime'] = row[3]
            results['endTime'] = row[4]
            results['content'] = row[5]

    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return results

# 新增/更新教育经历
def updateEduExp(param):
    conn = connect_db()
    cur = conn.cursor()
    cnt = 0
    try:
        sql = " update wms_edu_exp set member_id=%s, school=%s, education=%s, major=%s, start_time=%s, end_time=%s, school_exp=%s, update_time=%s where id=%s  "
        cnt = cur.execute(sql,(param['memberId'],param['school'],param['education'],param['major'],param['startTime'],param['endTime'],param['schoolExp'],datetime.datetime.now(),param['id']))
        if cnt == 0:
            sql = " insert into wms_edu_exp ( member_id, school, education, major, start_time, end_time, school_exp, create_time ) values ( %s, %s, %s, %s, %s, %s, %s, %s )"
            cnt = cur.execute(sql,(param['memberId'],param['school'],param['education'],param['major'],param['startTime'],param['endTime'],param['schoolExp'],datetime.datetime.now()))
        conn.commit()
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return cnt

# 删除教育经历
def deleteEduExpById(param):
    conn = connect_db()
    cur = conn.cursor()
    cnt = 0
    try:
        sql = " delete from wms_edu_exp where id = %s "
        cnt = cur.execute(sql,(param['id']))
        conn.commit()
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return cnt

# 新增/更新屏蔽公司
def addOrUpdateShield(param):
    conn = connect_db()
    cur = conn.cursor()
    cnt = 0
    results = {}
    try:
        sql = " select id from ums_shield_company where 1 = 1 and member_id = %s and type = %s and company_id = %s  "
        cnt = cur.execute(sql,(param['memberId'],0,param['companyId']))
        if cnt == 0:
            sql = "insert into ums_shield_company ( type, member_id, company_id, create_time ) values (%s, %s, %s, %s)"
            cur.execute(sql,(0,param['memberId'],param['companyId'],datetime.datetime.now()))
        else:
            results['msg'] = '已存在，无需重复添加'
        results['cnt'] = cnt
        conn.commit()
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return results

# 查询附件简历列表
def getResumeFileList(param):
    conn = connect_db()
    cur = conn.cursor()
    reclist = []
    try:         
        sql = " select id AS id, member_id AS memberId, file_name AS fileName, file_ext AS fileExt, file_size AS fileSize, file_url AS fileUrl, file_icon AS fileIcon, status AS status," \
              " DATE_FORMAT(create_time, '%%Y-%%m-%%d %%H:%%m:%%s') AS createTime from wms_resume_file where 1 = 1 and member_id = %s "
        cur.execute(sql,(param['memberId']))
        for row in cur.fetchall():
            results = {}
            results['id'] = row[0]
            results['memberId'] = row[1]
            results['fileName'] = row[2]
            results['fileExt'] = row[3]
            results['fileSize'] = row[4]
            results['fileUrl'] = row[5]
            results['fileIcon'] = row[6]
            results['status'] = row[7]
            results['createTime'] = row[8]
            reclist.append(results)

    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return reclist

# 删除附件简历
def deleteResumeFileById(param):
    conn = connect_db()
    cur = conn.cursor()
    cnt = 0
    try:
        sql = " delete from wms_resume_file where id = %s "
        cnt = cur.execute(sql,(param['id']))
        conn.commit()
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return cnt

# 获取上传简历文件名
def getfilename(param):
    conn = connect_db()
    cur = conn.cursor()
    filenm = ''
    try:
        sql = " select case when b.id is null then CONCAT('1' , a.member_id , a.name) else CONCAT(b.id , a.member_id , a.name) end as filename from wms_resume a left outer join " \
              " (select max(id) + 1 as id,member_id from wms_resume_file group by member_id) b on a.member_id = b.member_id  where 1 = 1 and a.member_id = %s "
        cur.execute(sql,(param['memberId']))
        for row in cur.fetchall():
            filenm = row[0] + "的简历." + param['fileExt']
        
        sql = "insert into wms_resume_file ( member_id, file_name , file_ext , file_size , file_url , create_time ) values (%s, %s, %s, %s, %s, %s)"
        cur.execute(sql,(param['memberId'],filenm.split('.')[0],param['fileExt'],param['fileSize'],param['fileUrl'] + filenm,datetime.datetime.now()))

        conn.commit()
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return filenm

# 导出简历
def exportWord(param):
    conn = connect_db()
    cur = conn.cursor()
    filenm = ''
    try:
        sql = " select case when b.id is null then CONCAT('1' , a.member_id , a.name) else CONCAT(b.id , a.member_id , a.name) end as filename from wms_resume a left outer join " \
              " (select max(id) + 1 as id,member_id from wms_resume_file group by member_id) b on a.member_id = b.member_id  where 1 = 1 and a.member_id = %s "
        cur.execute(sql,(param['memberId']))
        for row in cur.fetchall():
            filenm = row[0] + "的个人简历." + param['fileExt']
            
        sql = " select id AS id, name AS name, case when sex = 1 then '男' else '女' end AS sex,email AS email,browse_number AS browseNumber,skills AS skills,member_id AS memberId, phone AS phone, avatar AS avatar, education AS education " \
              "   , time_to_work AS timeToWork, work_year AS workYear, birthday AS birthday, salary_low AS salaryLow, salary_up AS salaryUp, company AS company, post_id AS postId " \
              "   , personal_skill AS personalSkill, job_status AS jobStatus, status AS status, remark AS remark " \
              " from wms_resume where 1 = 1 " 
        if param['id'] is not None:
            sql += " and id = " + str(param['id'])
        else:
            if param['memberId'] is not None:
                sql += " and member_id = " + str(param['memberId'])
        resume = {}    
        cur.execute(sql)
        for row in cur.fetchall():
            resume['id'] = row[0]
            resume['name'] = row[1]
            resume['sex'] = row[2]
            resume['email'] = row[3]
            resume['browseNumber'] = row[4]
            resume['skills'] = row[5]
            resume['memberId'] = row[6]
            resume['phone'] = row[7]
            resume['avatar'] = row[8]
            resume['education'] = row[9]
            resume['timeToWork'] = row[10]
            resume['workYear'] = row[11]
            resume['birthday'] = row[12]
            resume['salaryLow'] = row[13]
            resume['salaryUp'] = row[14]
            resume['company'] = row[15]
            resume['postId'] = row[16]
            resume['personalSkill'] = row[17]
            resume['jobStatus'] = row[18]
            resume['status'] = row[19]
            resume['remark'] = row[20]

            # 求职期望
            tmptext = ''
            sql = " select id AS id, member_id AS memberId,post_name AS postName,industry_name AS industryName,expect_salary AS expectSalary, job_type AS jobType, work_city AS workCity, post_id AS postId " \
                  "    , industry_id AS industryId, salary_low AS salaryLow, salary_up AS salaryUp, sort AS sort, status AS status, remark AS remark from wms_job_expect  where member_id = %s order by id desc  "
            cur.execute(sql,(resume['memberId']))
            for row in cur.fetchall():            
                job = {}
                job['id'] = row[0]
                job['memberId'] = row[1]
                job['postName'] = row[2]
                job['industryName'] = row[3]
                job['expectSalary'] = row[4]
                job['jobType'] = row[5]
                job['workCity'] = row[6]
                job['postId'] = row[7]
                job['industryId'] = row[8]
                job['salaryLow'] = row[9]
                job['salaryUp'] = row[10]
                job['sort'] = row[11]
                job['status'] = row[12]
                job['remark'] = row[13]
                tmptext += strval(job['postName']) + '（' + strval(job['expectSalary']) + '.' +  strval(job['industryName']) +  '）\n'
            resume['jobExpectList'] = tmptext.strip('\n')

            # 工作经历
            tmptext = ''
            i = 0
            sql = " select id AS id, member_id AS memberId,post_name AS postName,skill AS skill,industry_name AS industryName, company_name AS companyName, industry_id AS industryId, DATE_FORMAT(start_date, '%%Y.%%m') AS startDate " \
                  "    , DATE_FORMAT(end_date, '%%Y.%%m') AS endDate, post_id AS postId, job_content AS jobContent, department AS department, status AS status, remark AS remark from wms_work_exp where member_id = %s " \
                  "    order by id desc "
            cur.execute(sql,(resume['memberId']))
            for row in cur.fetchall():
                work = {}
                work['id'] = row[0]
                work['memberId'] = row[1]
                work['postName'] = row[2]
                work['skill'] = row[3]
                work['industryName'] = row[4]
                work['companyName'] = row[5]
                work['industryId'] = row[6]
                work['startDate'] = row[7]
                work['endDate'] = row[8]
                work['postId'] = row[9]
                work['jobContent'] = row[10]
                work['department'] = row[11]
                work['status'] = row[12]
                work['remark'] = row[13]
                tmptext += str(i + 1) + '.' + strval(work['startDate']) + ' - ' + strval(work['endDate']) + '  ' + strval(work['companyName']) + '  ' + strval(work['postName']) +'\n' \
                           + '  工作内容：' + strval(work['jobContent']) + '\n'
                i += 1
            resume['workExpList'] = tmptext.strip('\n')
            # 项目经历
            tmptext = ''
            i = 0
            sql = " select id AS id, member_id AS memberId, name AS name, role AS role, DATE_FORMAT(start_date, '%%Y.%%m') AS startDate, DATE_FORMAT(end_date, '%%Y.%%m') AS endDate, descr AS descr, performance AS performance, url AS url, status AS status " \
                  "    , remark AS remark from wms_pro_exp where member_id = %s  "
            cur.execute(sql,(resume['memberId']))
            for row in cur.fetchall():
                project = {}
                project['id'] = row[0]
                project['memberId'] = row[1]
                project['name'] = row[2]
                project['role'] = row[3]
                project['startDate'] = row[4]
                project['endDate'] = row[5]
                project['descr'] = row[6]
                project['performance'] = row[7]
                project['url'] = row[8]
                project['status'] = row[9]
                project['remark'] = row[10]
                tmptext += str(i + 1) + '.' + strval(project['startDate']) + ' - ' + strval(project['endDate']) + '  ' + strval(project['name']) + '  ' + strval(project['role']) + '\n' \
                           + '  内容：' + strval(project['descr']) + '\n' + '  业绩：' + strval(project['performance']) + '\n'
                i += 1              
            resume['proExpList'] = tmptext.strip('\n')
            
            # 教育经历
            tmptext = ''
            i = 0
            sql = " select id AS id, member_id AS memberId, school AS school, education AS education, if_full_time AS ifFullTime, major AS major, DATE_FORMAT(start_time, '%%Y.%%m') AS startTime, DATE_FORMAT(end_time, '%%Y.%%m') AS endTime, school_exp AS schoolExp " \
                  "    , status AS status, remark AS remark from wms_edu_exp where member_id = %s "
            cur.execute(sql,(resume['memberId']))
            for row in cur.fetchall():
                education = {}
                education['id'] = row[0]
                education['memberId'] = row[1]
                education['school'] = row[2]
                education['education'] = row[3]
                education['ifFullTime'] = row[4]
                education['major'] = row[5]
                education['startTime'] = row[6]
                education['endTime'] = row[7]
                education['schoolExp'] = row[8]
                education['status'] = row[9]
                education['remark'] = row[10]
                tmptext += str(i + 1) + '.' + strval(education['startTime']) + ' - ' + strval(education['endTime']) + '  ' + strval(education['school']) + '  ' + strval(education['education'])  + '  ' + strval(education['major']) + '\n' 
                i += 1 
            resume['eduExpList'] = tmptext.strip('\n')

        dir_path = str(os.path.abspath(os.path.dirname(__file__)))
        resumepath = os.path.join(dir_path.strip('wms'), 'resume')
        templatepath = os.path.join(dir_path.strip('wms'), 'templates')
        # 创建Jinja2环境
        env = Environment(loader=FileSystemLoader(templatepath))
        template = env.get_template('resume.html')
        # 渲染模板
        html_content = template.render(resume)
        # 生成PDF
        pdfkit.from_string(html_content, resumepath + '/' + filenm)

        file_size = getfilesize(os.stat(resumepath + '/' + filenm).st_size)
        
        sql = "insert into wms_resume_file ( member_id, file_name , file_ext , file_size , file_url , create_time ) values (%s, %s, %s, %s, %s, %s)"
        cur.execute(sql,(param['memberId'],filenm.split('.')[0],param['fileExt'],file_size,param['fileUrl'] + filenm,datetime.datetime.now()))

        conn.commit()
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return param['fileUrl'] + filenm

# 查询屏蔽公司列表
def getShieldList(param):
    conn = connect_db()
    cur = conn.cursor()
    rec = {}
    reclist = []
    try:         
        sql = " select a.id AS id, a.company_id AS companyId, b.name AS name, b.full_name AS fullname from ums_shield_company a inner join rms_company b on a.company_id = b.id where a.member_id = %s and a.type = 0"
        cur.execute(sql,(param['memberId']))
        for row in cur.fetchall():
            results = {}
            results['id'] = row[0]
            results['companyId'] = row[1]
            results['companyName'] = row[2]
            results['companyFullName'] = row[3]
            reclist.append(results)
        rec['count'] = len(reclist)
        rec['data'] = reclist
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return rec

# 删除屏蔽公司
def deleteShield(param):
    conn = connect_db()
    cur = conn.cursor()
    cnt = 0
    try:
        sql = " delete from ums_shield_company where id = %s "
        cnt = cur.execute(sql,(param['id']))
        conn.commit()
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return cnt

# 获取公司ID
def getCompanyId(companyid):
    conn = connect_db()
    cur = conn.cursor()
    rec = ''
    try:         
        sql = " select company_id from ums_member where member_id = %s "
        cur.execute(sql,(companyid))
        for row in cur.fetchall():
            rec = row[0]

    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return rec
